﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Configuration;
using PermitsAndVisasLeadsSyncher.Model;

namespace PermitsAndVisasLeadsSyncher.Helpers
{
    static class InfoProvider
    {
        public static string ReturnCountryId(List<PermitsAndVisasLeadsSyncher.Model.Country> countries, string countryName)
        {
            string str = string.Empty;
            foreach (Country country in countries)
            {
                if (country.Name == countryName)
                {
                    str = country.Id;
                    break;
                }
            }
            return str;
        }

        public static string AddPersonToCRMDataBaseAndReturnPersonId(string addPersonQuery, SqlCommand command)
        {
            command.CommandText = addPersonQuery;
            string personId = string.Empty;
            personId = DBHelper.ExecuteSqlScalar(addPersonQuery, command);
            return personId;
        }

        public static string AddnewCasetoCRMDatabaseAndReturnCaseId(string addNewCaseQuery, SqlCommand command)
        {
            command.CommandText = addNewCaseQuery;
            string caseId = string.Empty;
            caseId = DBHelper.ExecuteSqlScalar(addNewCaseQuery, command);
            return caseId;            
        }

        public static string SearchProductIdForCurrentlead(PermitsAndVisasLeadsSyncher.Model.Lead Leads, List<PermitsAndVisasLeadsSyncher.Model.ProductInfo> products)
        {
            string str = string.Empty;
            foreach (PermitsAndVisasLeadsSyncher.Model.ProductInfo productInfo in products)
            {
                if (Leads.DestinationCountryId == productInfo.ProductDestinationId)
                {
                    str = productInfo.ProductId;
                    break;
                }
            }
            if (string.IsNullOrEmpty(str))
                str = products.Find((Predicate<PermitsAndVisasLeadsSyncher.Model.ProductInfo>)(x => x.ProductTypeName.ToUpper() == "UAE-WWW")).ProductId;
            return str;
        }

        public static string FetchLastSucessfullLeadEDateFromDatabase()
        {
            string str = string.Empty;
            SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString);
            try
            {
                sqlConnection.Open();
                SqlCommand command = sqlConnection.CreateCommand();
                command.CommandText = PermitsAndVisasLeadsSyncher.Helpers.QueryProvider.MakeSqlQueryToFetchLastSucessfullLeadSynched();
                return command.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }
        }

        public static bool GetFirstLeadInfoCheck()
        {
            bool flag = false;
            SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString);
            try
            {
                sqlConnection.Open();
                SqlCommand command = sqlConnection.CreateCommand();
                command.CommandText = PermitsAndVisasLeadsSyncher.Helpers.QueryProvider.MakeSqlQueryToGetFirstLeadInfoCheckValue();
                object obj = command.ExecuteScalar();
                if (obj != null)
                    Convert.ToInt32(obj);
            }
            catch (InvalidCastException ex)
            {
                flag = true;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }
            return flag;
        }
    }
}
